package com.dhecp.project.logstics.service.impl;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.dhecp.common.config.Global;
import com.dhecp.common.core.domain.AjaxResult;
import com.dhecp.common.utils.MessageUtils;
import com.dhecp.common.utils.RandomValueUtils;
import com.dhecp.common.utils.StringUtils;
import com.dhecp.core.constant.DataFiltering;
import com.dhecp.core.utils.ExportExcelUtils;
import com.dhecp.core.utils.PageUtils;
import com.dhecp.framework.util.ShiroUtils;
import com.dhecp.project.business.domain.*;
import com.dhecp.project.business.mapper.FbaLogsticsOrderDetailMapper;
import com.dhecp.project.business.mapper.FbaLogsticsOrderMapper;
import com.dhecp.project.business.mapper.ProductBasisMapper;
import com.dhecp.project.common.domain.ModuleNidAndBid;
import com.dhecp.project.common.mapper.ModuleNidAndBidMapper;
import com.dhecp.project.common.service.impl.BaseModuleServiceImpl;
import com.dhecp.project.finance.domain.AccountCountForCompany;
import com.dhecp.project.finance.domain.AccountDetailedForCompany;
import com.dhecp.project.finance.mapper.AccountCountForCompanyMapper;
import com.dhecp.project.finance.mapper.AccountDetailedForCompanyMapper;
import com.dhecp.project.hr.domain.AmazonShop;
import com.dhecp.project.logstics.domain.*;
import com.dhecp.project.logstics.mapper.*;
import com.dhecp.project.logstics.service.FbaLogisticsOrderService;
import com.dhecp.project.storage.domain.HwTailprogramdetail;
import com.dhecp.project.system.domain.SysAccountBandDept;
import com.dhecp.project.system.domain.SysDictionaryForExport;
import com.dhecp.project.system.mapper.SysAccountBandDeptMapper;
import com.dhecp.project.system.service.SysDictionaryForFieldService;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.transaction.interceptor.TransactionAspectSupport;

import javax.imageio.ImageIO;
import java.awt.image.BufferedImage;
import java.io.*;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.net.URL;
import java.util.*;
import java.util.stream.Collectors;

/**
 * @Author fxz
 * @Date 2022/10/27
 */
@Service
public class FbaLogisticsOrderServicelmpl extends BaseModuleServiceImpl implements FbaLogisticsOrderService {


    @Autowired
    private SysDictionaryForFieldService dictionaryForFieldService;

    @Autowired
    private FbaLogsticsOrderMapper fbaLogsticsOrderMapper;

    @Autowired
    private FbaLogsticsOrderDetailMapper fbaLogsticsOrderDetailMapper;

    @Autowired
    private FbaBoxDetailMapper fbaBoxDetailMapper;

    @Autowired
    private ProductBasisMapper productBasisMapper;

    @Autowired
    private FbaBoxMapper fbaBoxMapper;

    @Autowired
    private FbaLogsticsOrderBoxMapper fbaLogsticsOrderBoxMapper;

    @Autowired
    private FbaSpellTickMapper fbaSpellTickMapper;

    @Autowired
    private FbaSpellTicketDetailMapper fbaSpellTicketDetailMapper;

    @Autowired
    private SysAccountBandDeptMapper sysAccountBandDeptMapper;

    @Autowired
    private AccountCountForCompanyMapper accountCountForCompanyMapper;

    @Autowired
    private AccountDetailedForCompanyMapper businessMapper;

    @Autowired
    private ModuleNidAndBidMapper moduleNidAndBidMapper;

    @Autowired
    private FbaWarehousingMapper fbaWarehousingMapper;

    @Autowired
    private FbaWarehousingDetailMapper fbaWarehousingDetailMapper;

    /**
     * 查询物流下单
     * @param params
     * @return
     * @throws Exception
     */
    @Override
    public List<FbaLogsticsOrder> queryList(Map<String, Object> params) throws Exception {
        //特殊字段fba修改查询详情有关联的数据
        if (params.get("ShipmentId") != null && !params.get("ShipmentId").equals("")){
            params.put("ShipmentId1",params.get("ShipmentId"));
            params.put("ShipmentId","");
        }
        params.put(DataFiltering.TABLE_ALIAS, "t1");	//查询表别名
        params.put(DataFiltering.DATA_WHERE, dictionaryForFieldService.getWhereStr(params));	//常规查询条件SQL语句
        params.put(DataFiltering.DATA_SCODE, dictionaryForFieldService.getDataScode(params));	//数据权限查询条件SQL语句

        if (params.get("field") == null || params.get("field") == ""){
            String orderBy = "t1.code DESC";
            PageUtils.startPageOrder(orderBy);
        }else{
            PageUtils.startPage();
        }

        return fbaLogsticsOrderMapper.findFbaLogsticsOrder(params);
    }


    /**
     *@Description: 打印发票
     *@Author: fxz
     *@date: 2023-05-24 10:11
     */
    @Override
    @Transactional(timeout=120,propagation= Propagation.REQUIRES_NEW)
    public AjaxResult printList(Map<String, Object> params) {
        params.put("moduleTable", "TP_ListInvoices");
        params.put("entity", new FbaBox());

        //获取标题
        List<SysDictionaryForExport> fieldList = dictionaryForFieldService.queryExportFieldList(params);
        List<FbaLogsticsOrder> fbaLogsticsOrderList =  JSON.parseArray(params.get("infoItems").toString(), FbaLogsticsOrder.class);
        if (fbaLogsticsOrderList.size() == 0){
            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
            return AjaxResult.error("物流订单数据不存在!");
        }
        List<FbaLogsticsOrder> fbaLogsticsOrderListNew = fbaLogsticsOrderMapper.findFbaLogsticsOrderBatch(fbaLogsticsOrderList);
        for (FbaLogsticsOrder fbaLogsticsOrder : fbaLogsticsOrderListNew){
            //修改发票清单状态
            if (fbaLogsticsOrder.getStateTrack().equals("1")){
                fbaLogsticsOrder.setStateTrack("2");
                fbaLogsticsOrder.setStateTrackName("发票清单已打出");
            }
        }
        List<FbaLogsticsOrderDetail> fbaLogsticsOrderDetailList = fbaLogsticsOrderDetailMapper.findFbaLogsticsOrderDetailBatch(fbaLogsticsOrderList);
        FbaBox fbaBox1;
        List<FbaBox> fbaBoxList = new ArrayList<>();
        for (FbaLogsticsOrderDetail fbaLogsticsOrderDetail : fbaLogsticsOrderDetailList){
            if (fbaLogsticsOrderDetail.getStateTrack().equals("1")){
                fbaLogsticsOrderDetail.setStateTrack("2");
                fbaLogsticsOrderDetail.setStateTrackName("发票清单已打出");
            }
            fbaBox1 = new FbaBox();
            fbaBox1.setCode(fbaLogsticsOrderDetail.getCode());
            fbaBoxList.add(fbaBox1);
        }
        List<FbaBox> fbaBox1List = fbaBoxMapper.findFbaBoxBatch(fbaBoxList);
        if (fbaBoxList.size() != fbaBox1List.size()){
            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
            return AjaxResult.error("选择的头程装箱单不存在,请核实后在操作!");
        }
        List<PrintList2> printBatchList = fbaBoxMapper.printListBatchGuid(fbaBox1List);

        List<FbaBoxDetail> fbaBoxDetailBatchList  = fbaBoxDetailMapper.fbaBoxDetailBatchList(fbaBox1List);

        Set<String> productBases = new HashSet<>();
        Set<String> fnSkuList = new HashSet<>();
        Map<String, Set<String>> tmpCondition = new HashMap<>();
        List<FbaBoxDetail> fbaBoxDetailListnew;
        for (FbaBoxDetail fbaBoxDetail : fbaBoxDetailBatchList) {
            //获取商品信息
            fbaBoxDetailListnew = JSON.parseArray(fbaBoxDetail.getProductInfo(), FbaBoxDetail.class);
            for (FbaBoxDetail fbaBoxDetai : fbaBoxDetailListnew) {
                productBases.add(fbaBoxDetai.getCommodityCode());
                fnSkuList.add(fbaBoxDetai.getFnsku());
            }
        }
        tmpCondition.put("productBases",productBases);
        tmpCondition.put("fnSkuList",fnSkuList);
        if (tmpCondition.size() == 0){
            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
            return AjaxResult.error("箱子数据不能为空,打印失败!");
        }
        List<ProductBasis> productActiveBatchList  = productBasisMapper.findOnlineStoreProductsBatch1(tmpCondition);

        List<PrintList2> printList;
        PrintList2 printList2;
        List<ProductBasis> productActiveList = null;
        List<FbaBoxDetail> fbaBoxDetailList;
        ProductBasis productActive = null;
        PrintList2 printList3;
        List<PrintList2> printList2s = new ArrayList<>();
        String filename = null;
        List<FbaBox> fbaBox1ListNew = new ArrayList<>();
        List<FbaLogsticsOrderDetail> fbaLogsticsOrderDetailListNew;
        List<FbaLogsticsOrder> fbaLogsticsOrderListNew1;
        for (FbaBox fbaBox : fbaBox1List) {
            if (fbaBox.getStateTrack().equals("1")){
                fbaBox.setStateTrack("2");
                fbaBox.setStateTrackName("发票清单已打出");
                fbaBox1ListNew.add(fbaBox);
            }
            //装箱数据
            printList = printBatchList.stream().filter(print -> print.getGuid().equals(fbaBox.getGuid()))
                    .collect(Collectors.toList());
            if (printList.size() == 0) {
                TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
                return AjaxResult.error("头程装箱:" + fbaBox.getCode() + "不存在!");
            }
            printList2 = printList.get(0);

            //头程计划单下所有箱子数据
            fbaBoxDetailList = fbaBoxDetailBatchList.stream().filter(fbaBoxDetail -> fbaBoxDetail.getCodeBox().equals(fbaBox.getCode()))
                    .collect(Collectors.toList());
            if (fbaBoxDetailList.size() == 0) {
                TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
                return AjaxResult.error("箱子数据不存在!");
            }

            //获取这一单订单详情
            fbaLogsticsOrderDetailListNew = fbaLogsticsOrderDetailList.stream().filter(
                    fbaLogsticsOrderDetail -> fbaLogsticsOrderDetail.getCode().equals(fbaBox.getCode()))
                    .collect(Collectors.toList());
            if (fbaLogsticsOrderDetailListNew.size() == 0) {
                TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
                return AjaxResult.error("订单详情数据不存在!");
            }
            //获取这一单订单信息
            FbaLogsticsOrderDetail finalFbaLogsticsOrderDetail = fbaLogsticsOrderDetailListNew.get(0);
            fbaLogsticsOrderListNew1 = fbaLogsticsOrderList.stream().filter(
                    fbaLogsticsOrder -> fbaLogsticsOrder.getCode().equals(finalFbaLogsticsOrderDetail.getCodeFbaLogsticsOrder()))
                    .collect(Collectors.toList());
            if (fbaLogsticsOrderListNew1.size() == 0) {
                TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
                return AjaxResult.error("订单数据不存在!");
            }

            int beginNum = 1;
            int endNum = 0;
            for (FbaBoxDetail fbaBoxDetail11 : fbaBoxDetailList) {
                beginNum += 1;
                //获取商品信息
                fbaBoxDetailListnew = JSON.parseArray(fbaBoxDetail11.getProductInfo(), FbaBoxDetail.class);
                //进行排序（naturalOrder 正序）（reverseOrder倒叙）
                fbaBoxDetailListnew = fbaBoxDetailListnew.stream().
                        sorted(Comparator.comparing(FbaBoxDetail::getCommodityCode, Comparator.naturalOrder())).
                        collect(Collectors.toList());
                endNum = beginNum + fbaBoxDetailListnew.size() - 1;
                for (FbaBoxDetail fbaBoxDetail : fbaBoxDetailListnew) {
                    productActiveList = productActiveBatchList.stream().filter(productBasis -> productBasis.getCode().equals(fbaBoxDetail.getCommodityCode()))
                            .collect(Collectors.toList());
                    if (productActiveList.size() == 0) {
                        TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
                        return AjaxResult.error("商品库信息不存在!");
                    }
//                    if (productActiveList.size() > 1) {
//                        TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
//                        return AjaxResult.error("商品库信息大于2条数据错误,请联系管理员!");
//                    }
                    productActive = productActiveList.get(0);
                    printList3 = new PrintList2();
                    BeanUtils.copyProperties(printList2, printList3);

                    if (printList2.getDestinationFulfillmentCente() == null) {
                        TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
                        return AjaxResult.error("亚马逊地址不存在,请维护后导出!");
                    }
                    ArrayList<String> arrayList = new ArrayList<String>();
                    arrayList.add(printList2.getName1());
                    arrayList.add(printList2.getAddressLine11());
                    arrayList.add(printList2.getCity1() + "," + printList2.getStateOrProvinceCode1() + " " + printList2.getPostalCode1());
                    arrayList.add(printList2.getCountryCode1() + " (" + printList2.getDestinationFulfillmentCente() + ")");
                    String content = String.join("\n", arrayList);
                    printList3.setHarvestAddress(content);

                    arrayList = new ArrayList<String>();
                    arrayList.add(printList2.getName());
                    arrayList.add(printList2.getAddressLine1());
                    arrayList.add(printList2.getAddressLine2());
                    arrayList.add(printList2.getCity() + "," + printList2.getStateOrProvinceCode() + " " + printList2.getPostalCode());
                    arrayList.add("CN");
//                    arrayList.add(printList2.getCountryCode());
                    content = String.join("\n", arrayList);
                    printList3.setShipAddress(content);

                    printList3.setBeginNum(beginNum);
                    printList3.setEndNum(endNum);
                    printList3.setContainerNo(fbaBox.getShipmentId() + "U00000" + (fbaBoxDetail11.getBoxIndex() + 1));
                    printList3.setLength(fbaBoxDetail11.getLength());
                    printList3.setWidth(fbaBoxDetail11.getWidth());
                    printList3.setHeight(fbaBoxDetail11.getHeight());
                    printList3.setWeight(fbaBoxDetail11.getWeight());
                    printList3.setWeightVol(fbaBoxDetail11.getWeightVol());
                    printList3.setIndexDumping(Double.valueOf(fbaBoxDetail11.getWeightVol()) - Double.valueOf(fbaBoxDetail11.getWeight()));
                    printList3.setBrand("N/A");
                    printList3.setModel(productActive.getCode());
                    printList3.setProductPicture(productActive.getImg());
                    printList3.setNameCn(productActive.getNameCn());
                    printList3.setNameEn(productActive.getNameEn());
                    printList3.setCodeHs(productActive.getCodeHs());
                    printList3.setMaterialCn(productActive.getMaterialCn());
                    printList3.setMaterialEn(productActive.getMaterialEn());
                    printList3.setUsageCn(productActive.getUsageCn());
                    printList3.setUsageEn(productActive.getUsageEn());
                    if(productActive.getSizeWeight() != null){
                        printList3.setSizeWeight(Double.valueOf(String.format("%.3f", productActive.getSizeWeight()/1000)));
                    }
                    printList3.setPriceIn(Double.valueOf(fbaBoxDetail.getPriceIn()));
                    printList3.setNameUnit(productActive.getNameUnit());
                    printList3.setLinkJP(fbaBoxDetail.getLinkJP());
                    printList3.setIsSign(fbaBoxDetail.getIsSign());
                    printList3.setNumPlanDeliver(fbaBoxDetail.getNumRealityDeliver());
                    if (!printList3.getNumPlanDeliver().equals("0") && !printList3.getNumPlanDeliver().equals("")){
                        printList2s.add(printList3);
                    }
                    //添加物流服务商 物流名称
                    printList3.setCodeLogsticsServer(fbaLogsticsOrderListNew1.get(0).getCodeLogsticsServer());
                    printList3.setNameLogsticsWayNew(fbaLogsticsOrderListNew1.get(0).getNameLogsticsWay());
                }
                beginNum = endNum;
            }
        }
        OutputStream out = null;
        HSSFWorkbook wb = null;
        try {
            //创建HSSFWorkbook对象
            wb = new HSSFWorkbook();
            //建立sheet对象
            HSSFSheet sheet = wb.createSheet("发票清单表");
            //在sheet里创建第一行，参数为行索引
            HSSFRow row1 = sheet.createRow(0);
            HSSFCell cell;
            HSSFCellStyle firstStyle = null;
            HSSFFont firstFont = null;
            for (int i = 0; i < fieldList.size(); i++) {
                //创建单元格
                cell = row1.createCell(i);
                //设置单元格内容
                cell.setCellValue(fieldList.get(i).getFieldName());
                if (i == 0){
                    firstStyle = wb.createCellStyle();
                    firstFont = wb.createFont();
                    addHSSFWorkbook(firstStyle,firstFont,wb, cell, 2, "宋体", false, (short) 11,false);
                } else {
                    addHSSFWorkbook(firstStyle,firstFont,wb, cell, 2, "宋体", false, (short) 11,false);
                }
                sheet.setColumnWidth(i, 100 * 48);
            }
            HSSFRow row2;
            for (int i = 0; i < printList2s.size(); i++) {
                //采购合计
                String purchasePrice = "";
                String weightTol = "";
                for (int k = printList2s.get(i).getBeginNum(); k <= printList2s.get(i).getEndNum(); k++) {
                    if (k==printList2s.get(i).getBeginNum()){
                        purchasePrice = "AL"+k+"*"+"AO"+k;
                        weightTol = "AK"+k+"*"+"AO"+k;
                    }else {
                        purchasePrice = purchasePrice + "+" +"AL"+k+"*"+"AO"+k;
                        weightTol = weightTol + "+" +"AK"+k+"*"+"AO"+k;
                    }
                }
                row2 = sheet.createRow(i+1);
                for (int j = 0; j < fieldList.size(); j++) {
                    //创建单元格
                    cell = row2.createCell(j);
                    //设置单元格内容
                    if (fieldList.get(j).getFields().equals("code")){
                        cell.setCellValue(printList2s.get(i).getCode());
                    } else if (fieldList.get(j).getFields().equals("fpoUserName")){
                        cell.setCellValue(printList2s.get(i).getFpoUserName());
                    }else if (fieldList.get(j).getFields().equals("nameLogsticsPattern")){
                        cell.setCellValue(printList2s.get(i).getNameLogsticsPattern());
                    }else if (fieldList.get(j).getFields().equals("nameLogsticsWay")){
                        cell.setCellValue(printList2s.get(i).getNameLogsticsWay());
                    }else if (fieldList.get(j).getFields().equals("nameLogsticsClearance")){
                        cell.setCellValue(printList2s.get(i).getNameLogsticsClearance());
                    }else if (fieldList.get(j).getFields().equals("vGsMc")){
                        cell.setCellValue(printList2s.get(i).getVGsMc());
                    }else if (fieldList.get(j).getFields().equals("vGsJc")){
                        cell.setCellValue(printList2s.get(i).getVGsJc());
                    }else if (fieldList.get(j).getFields().equals("Phone")){
                        if(isNumber(printList2s.get(i).getPhone())){
                            cell.setCellValue(Double.valueOf(printList2s.get(i).getPhone()));
                        } else{
                            cell.setCellValue(printList2s.get(i).getPhone());
                        }
                    }else if (fieldList.get(j).getFields().equals("ShipAddress")){
                        cell.setCellValue(printList2s.get(i).getShipAddress());
                    }else if (fieldList.get(j).getFields().equals("HarvestAddress")){
                        cell.setCellValue(printList2s.get(i).getHarvestAddress());
                    }else if (fieldList.get(j).getFields().equals("VAT")){
                        cell.setCellValue(printList2s.get(i).getVAT());
                    }else if (fieldList.get(j).getFields().equals("EORI")){
                        cell.setCellValue(printList2s.get(i).getEORI());
                    }else if (fieldList.get(j).getFields().equals("destinationFulfillmentCenterId")){
                        cell.setCellValue(printList2s.get(i).getDestinationFulfillmentCente());
                    }else if (fieldList.get(j).getFields().equals("shipmentId")){
                        cell.setCellValue(printList2s.get(i).getShipmentId());
                    }else if (fieldList.get(j).getFields().equals("trackingCode")){
                        cell.setCellValue(printList2s.get(i).getTrackingCode());
                    }else if (fieldList.get(j).getFields().equals("numBox")){
                        cell.setCellValue(Double.valueOf(printList2s.get(i).getNumBox()));
                    }else if (fieldList.get(j).getFields().equals("ContainerNo")){
                        cell.setCellValue(printList2s.get(i).getContainerNo());
                    }else if (fieldList.get(j).getFields().equals("Length")){
                        cell.setCellValue(Double.valueOf(printList2s.get(i).getLength()));
                    }else if (fieldList.get(j).getFields().equals("Width")){
                        cell.setCellValue(Double.valueOf(printList2s.get(i).getWidth()));
                    }else if (fieldList.get(j).getFields().equals("Height")){
                        cell.setCellValue(Double.valueOf(printList2s.get(i).getHeight()));
                    }else if (fieldList.get(j).getFields().equals("weightVol")){
                        cell.setCellValue(Double.valueOf(printList2s.get(i).getWeightVol()));
                    }else if (fieldList.get(j).getFields().equals("Weight")){
                        cell.setCellValue(Double.valueOf(printList2s.get(i).getWeight()));
                    }else if (fieldList.get(j).getFields().equals("IndexDumping")){
                        cell.setCellFormula("T"+(i+2)+"-"+"U"+(i+2));
                    }else if (fieldList.get(j).getFields().equals("PurchasePrice")){
                        cell.setCellFormula("ROUND("+purchasePrice+",2)");
                    }else if (fieldList.get(j).getFields().equals("PurchasePrice1")){
                        cell.setCellFormula("ROUND(("+purchasePrice+")" + "/9,2)");
                    }else if (fieldList.get(j).getFields().equals("PurchasePrice2")){
                        cell.setCellFormula("ROUND(("+purchasePrice+")"+"/6.4,2)");
                    }else if (fieldList.get(j).getFields().equals("TotalWeight")){
                        cell.setCellFormula("ROUND("+weightTol+",2)");
                    }else if (fieldList.get(j).getFields().equals("Brand")){
                        cell.setCellValue("N/A");
                    }else if (fieldList.get(j).getFields().equals("Model")){
                        cell.setCellValue(printList2s.get(i).getModel());
                    }else if (fieldList.get(j).getFields().equals("ProductPicture")){
                        try {
                            if(printList2s.get(i).getProductPicture() == null){
                                cell.setCellValue("");
                            }else {
                                addPhotograph(sheet,wb,printList2s.get(i).getProductPicture(),i+1);
                            }
                        }catch (Exception e){
                            if (e.getMessage().equals("Can't get input stream from URL!")){
                                cell.setCellValue("");
                            }
                        }
                    }else if (fieldList.get(j).getFields().equals("nameCn")){
                        cell.setCellValue(printList2s.get(i).getNameCn());
                    }else if (fieldList.get(j).getFields().equals("nameEn")){
                        cell.setCellValue(printList2s.get(i).getNameEn());
                    }else if (fieldList.get(j).getFields().equals("codeHs")){
                        if(isNumber(printList2s.get(i).getCodeHs())){
                            cell.setCellValue(Double.valueOf(printList2s.get(i).getCodeHs()));
                        } else{
                            cell.setCellValue(printList2s.get(i).getCodeHs());
                        }
                    }else if (fieldList.get(j).getFields().equals("materialCn")){
                        cell.setCellValue(printList2s.get(i).getMaterialCn());
                    }else if (fieldList.get(j).getFields().equals("materialEn")){
                        cell.setCellValue(printList2s.get(i).getMaterialEn());
                    }else if (fieldList.get(j).getFields().equals("usageCn")){
                        cell.setCellValue(printList2s.get(i).getUsageCn());
                    }else if (fieldList.get(j).getFields().equals("usageEn")){
                        cell.setCellValue(printList2s.get(i).getUsageEn());
                    }else if (fieldList.get(j).getFields().equals("sizeWeight")){
                        if (printList2s.get(i).getSizeWeight() != null){
                            cell.setCellValue(Double.valueOf(printList2s.get(i).getSizeWeight()));
                        }
                    }else if (fieldList.get(j).getFields().equals("priceIn")){
                        if (printList2s.get(i).getPriceIn() != null){
                            cell.setCellValue(Double.valueOf(printList2s.get(i).getPriceIn()));
                        }
                    }else if (fieldList.get(j).getFields().equals("nameUnit")){
                        cell.setCellValue(printList2s.get(i).getNameUnit());
                    }else if (fieldList.get(j).getFields().equals("linkJP")){
                        cell.setCellValue(printList2s.get(i).getLinkJP());
                    }else if (fieldList.get(j).getFields().equals("numPlanDeliver")){
                        if (printList2s.get(i).getNumPlanDeliver() != null){
                            cell.setCellValue(Double.valueOf(printList2s.get(i).getNumPlanDeliver().equals("")?"0":printList2s.get(i).getNumPlanDeliver()));
                        }
                    }else if (fieldList.get(j).getFields().equals("IsSign")){
                        cell.setCellValue(printList2s.get(i).getIsSign());
                    }else if (fieldList.get(j).getFields().equals("nameLogsticsWayNew")){
                        cell.setCellValue(printList2s.get(i).getNameLogsticsWayNew());
                    }else if (fieldList.get(j).getFields().equals("codeLogsticsServer")){
                        cell.setCellValue(printList2s.get(i).getCodeLogsticsServer());
                    }
                    if (i == 0){
                        firstStyle = wb.createCellStyle();
                        firstFont = wb.createFont();
                        addHSSFWorkbook(firstStyle,firstFont,wb, cell, 1, "宋体", false, (short) 11, false);
                    } else {
                        addHSSFWorkbook(firstStyle,firstFont,wb, cell, 1, "宋体", false, (short) 11, false);
                    }

                }
            }



            filename = UUID.randomUUID().toString() + "_" + "发票清单" + ".xlsx";
            String downloadPath = Global.getDownloadPath() + filename;
            File desc = new File(downloadPath);
            if (!desc.getParentFile().exists())
            {
                desc.getParentFile().mkdirs();
            }
            out = new FileOutputStream(downloadPath);
//                out = new FileOutputStream("C:/Users/Administrator/Desktop" + "/" + 1 + ".xlsx");
            wb.write(out);
//                out.close();

            //修改物流状态为发票清单已导入
            if (fbaBox1ListNew.size() != 0){
                fbaBoxMapper.updateBatchGuid(fbaBox1ListNew);
            }

            if (fbaLogsticsOrderListNew.size() != 0){
                fbaLogsticsOrderMapper.batchUpdate(fbaLogsticsOrderListNew);
            }
            if (fbaLogsticsOrderDetailList.size() != 0){
                int updateLength = fbaLogsticsOrderDetailList.size();
                int i = 0;
                while (updateLength > 90) {
                    fbaLogsticsOrderDetailMapper.batchUpdateNew(fbaLogsticsOrderDetailList.subList(i, i + 90));
                    i = i + 90;
                    updateLength = updateLength - 90;
                }
                if (updateLength > 0) {
                    fbaLogsticsOrderDetailMapper.batchUpdateNew(fbaLogsticsOrderDetailList.subList(i, i + updateLength));
                }
            }

            return AjaxResult.success(filename);
        }catch (Exception e) {
            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
            return AjaxResult.error("导出发票清单失败!");
        }
        finally{
            if (wb != null){
                try{
                    wb.close();
                }
                catch (IOException e1){
                    e1.printStackTrace();
                }
            }
            if (out != null){
                try{
                    out.close();
                }
                catch (IOException e1){
                    e1.printStackTrace();
                }
            }
        }

    }


    private HSSFCellStyle addHSSFWorkbook(HSSFCellStyle firstStyle,HSSFFont firstFont,HSSFWorkbook wb, HSSFCell cell,Integer alignment,String fontName,boolean bold,short fontHeightInPoints,boolean wrapText){
        if (alignment == 1){
            firstStyle.setAlignment(HorizontalAlignment.CENTER); // 创建一个居中格式
            firstStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
        } else if (alignment == 2){
            firstStyle.setAlignment(HorizontalAlignment.CENTER); // 创建一个居中格式
            firstStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
            firstStyle.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());	//更改背景颜色
            firstStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        }

        addHSSFCellStyle(firstStyle);

        firstFont.setFontName(fontName);
        firstFont.setBold(bold);
        firstFont.setFontHeightInPoints(fontHeightInPoints);
        if (alignment == 2){
            firstFont.setColor(IndexedColors.WHITE.getIndex());
        }
        firstStyle.setFont(firstFont);
        firstStyle.setWrapText(wrapText); //设置换行
        cell.setCellStyle(firstStyle);
        return firstStyle;
    }

    private HSSFCellStyle addHSSFCellStyle(HSSFCellStyle firstStyle){
        firstStyle.setBorderRight(BorderStyle.THIN);	//设置单元格右边框的边框类型
        firstStyle.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());	//设置右边边框的颜色
        firstStyle.setBorderLeft(BorderStyle.THIN);	//设置单元格左边框的边框类型
        firstStyle.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());	//设置左边框的颜色
        firstStyle.setBorderTop(BorderStyle.THIN);	//设置单元格上边框使用的边框类型
        firstStyle.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());	//设置上边框的颜色
        firstStyle.setBorderBottom(BorderStyle.THIN);	//设置单元格下边框使用的边框类型
        firstStyle.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());	//设置下边框的颜色
        return firstStyle;
    }

    private  boolean isNumber(String str) {

        if (StringUtils.isNotEmpty(str)) {
            String reg = "^[0-9]+(.[0-9]+)?$";
            return str.matches(reg);
        }
        return false;
    }


    /**
     * 导入并直接回填追踪编号单据
     * @return
     * @author fxz
     */
    @Override
    @Transactional(timeout=120,propagation= Propagation.REQUIRES_NEW)
    public AjaxResult importData(Map<String, Object> params) {
        JSONArray jsonArray = JSON.parseArray(params.get("infoItems").toString());
        Map<String, List<String>> params1 = null;
        Map<String, Map<String, List<String>>> params2 = new HashMap<String, Map<String, List<String>>>();
        List<String> stateTrackCodeList;
        String shipmentId = "";
        String stateTrackCode = "";
        String codeFbaLogsticsOrder = "";
        List<FbaLogsticsOrder> fbaLogsticsOrderList = new ArrayList<>();
        FbaLogsticsOrder fbaLogsticsOrder;
        for (int i = 0; i < jsonArray.size(); i++) {
            JSONObject jsonObject = (JSONObject) jsonArray.get(i);
            if (jsonObject.get("shipmentId") != null){
                shipmentId = jsonObject.get("shipmentId").toString();
                stateTrackCode = jsonObject.get("stateTrackCode").toString();
                stateTrackCode = stateTrackCode.trim();
                codeFbaLogsticsOrder = jsonObject.get("codeFbaLogsticsOrder").toString();
                if (shipmentId.indexOf("FBA") == -1){
                    TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
                    return AjaxResult.error("导入数据出错");
                } else {
                    if (params2.get(codeFbaLogsticsOrder) == null){
                        params1 = new HashMap<String, List<String>>();
                        stateTrackCodeList = new ArrayList<>();
                        stateTrackCodeList.add(stateTrackCode);
                        params1.put(shipmentId,stateTrackCodeList);
                    } else {
                        if (params2.get(codeFbaLogsticsOrder).get(shipmentId) == null){
                            stateTrackCodeList = new ArrayList<>();
                        } else {
                            stateTrackCodeList = params2.get(codeFbaLogsticsOrder).get(shipmentId);
                        }
                        params1 = params2.get(codeFbaLogsticsOrder);
                        stateTrackCodeList.add(stateTrackCode);
                        params1.put(shipmentId,stateTrackCodeList);
                    }
                    params2.put(codeFbaLogsticsOrder,params1);
                    fbaLogsticsOrder = new FbaLogsticsOrder();
                    fbaLogsticsOrder.setCode(codeFbaLogsticsOrder);
                    fbaLogsticsOrderList.add(fbaLogsticsOrder);
                }
            }
        }

        //物流订单信息
        List<FbaLogsticsOrder> fbaLogsticsOrderBatch = fbaLogsticsOrderMapper.findFbaLogsticsOrderBatch(fbaLogsticsOrderList);
        if (fbaLogsticsOrderBatch.size() == 0){
            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
            return AjaxResult.error("订单不存在,请核实后再操作!");
        }
        //物流详情信息
        List<FbaLogsticsOrderDetail> fbaLogsticsOrderDetailBatch = fbaLogsticsOrderDetailMapper.findFbaLogsticsOrderDetailBatch(fbaLogsticsOrderList);
        //物流箱子信息
        List<FbaLogsticsOrderBox> fbaLogsticsOrderBoxBatch = fbaLogsticsOrderBoxMapper.findFbaLogsticsOrderBoxBatch(fbaLogsticsOrderList);
        //头程完成单数据
        List<FbaBox> findFbaBoxBatchList = fbaBoxMapper.findFbaBoxBatch1(fbaLogsticsOrderDetailBatch);
        //头程完成单箱子数据
        List<FbaBoxDetail> findFbaBoxDetailBatchList = fbaBoxDetailMapper.findFbaBoxDetailBatch1(fbaLogsticsOrderDetailBatch);

        List<FbaBox> findFbaBoxNewList;
        List<FbaBoxDetail> findFbaBoxDetailNewList;
        List<FbaLogsticsOrder> fbaLogsticsOrderNewList;
        List<FbaLogsticsOrderDetail> fbaLogsticsOrderDetailNewList;
        List<FbaLogsticsOrderBox> fbaLogsticsOrderBoxNewList;
        for (Map.Entry<String,Map<String, List<String>>> entry: params2.entrySet()) {
            fbaLogsticsOrderNewList = fbaLogsticsOrderBatch.stream().filter(fbaLogsticsOrder1 -> fbaLogsticsOrder1.getCode().equals(entry.getKey()))
                    .collect(Collectors.toList());
            if (fbaLogsticsOrderNewList.size() == 0){
                TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
                return AjaxResult.error("订单号:"+entry.getKey()+"数据不存在,请核实后再操作!");
            }
            if (!fbaLogsticsOrderNewList.get(0).getStateTrack().equals("4")){
                if (!fbaLogsticsOrderNewList.get(0).getStateTrack().equals("3")){
                    TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
                    return AjaxResult.error("订单号:"+entry.getKey()+"物流状态不是已发货,不允许导入追踪编号!");
                }
            }

            fbaLogsticsOrderNewList.get(0).setStateTrack("4");
            fbaLogsticsOrderNewList.get(0).setStateTrackName("追踪编号已导入");

            fbaLogsticsOrderDetailNewList = fbaLogsticsOrderDetailBatch.stream().filter(fbaLogsticsOrderDetail -> fbaLogsticsOrderDetail.getCodeFbaLogsticsOrder().equals(entry.getKey()))
                    .collect(Collectors.toList());
            if (fbaLogsticsOrderDetailNewList.size() != entry.getValue().size()){
                TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
                return AjaxResult.error("订单号:"+entry.getKey()+"实际FBA编号与上传数据FBA编号数据不匹配,请核实后再操作!");
            }

            for (FbaLogsticsOrderDetail fbaLogsticsOrderDetail : fbaLogsticsOrderDetailNewList){
                fbaLogsticsOrderDetail.setTrackingTime(new Date());
                fbaLogsticsOrderDetail.setStateTrack("4");
                fbaLogsticsOrderDetail.setStateTrackName("追踪编号已导入");

                //修改头程完成单信息
                findFbaBoxNewList = findFbaBoxBatchList.stream().filter(fbaBox -> fbaBox.getCode().equals(fbaLogsticsOrderDetail.getCode()))
                        .collect(Collectors.toList());
                findFbaBoxNewList.get(0).setTrackingTime(new Date());
                findFbaBoxNewList.get(0).setStateTrack("4");
                findFbaBoxNewList.get(0).setStateTrackName("追踪编号已导入");

                fbaLogsticsOrderBoxNewList = fbaLogsticsOrderBoxBatch.stream().filter(fbaLogsticsOrderBox -> fbaLogsticsOrderBox.getLogsticsOrderDetailGuid().equals(fbaLogsticsOrderDetail.getGuid()
                    )).collect(Collectors.toList());

                if (entry.getValue().get(fbaLogsticsOrderDetail.getShipmentId()) == null){
                    TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
                    return AjaxResult.error("物流订单编码:"+fbaLogsticsOrderDetail.getCodeFbaLogsticsOrder()+"上传FBA数据不匹配,请核实后再操作!");
                }
                if (fbaLogsticsOrderBoxNewList.size() != entry.getValue().get(fbaLogsticsOrderDetail.getShipmentId()).size()){
                    TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
                    return AjaxResult.error("FBA编号:"+fbaLogsticsOrderDetail.getShipmentId()+"实际头程箱数与上传数据追踪编号数据不匹配,请核实后再操作!");
                }
                for (int i = 0; i < fbaLogsticsOrderBoxNewList.size(); i++) {
                    stateTrackCodeList = entry.getValue().get(fbaLogsticsOrderDetail.getShipmentId());
                    if (stateTrackCodeList.equals("") || stateTrackCodeList == null){
                        TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
                        return AjaxResult.error("FBA编号:"+fbaLogsticsOrderDetail.getShipmentId()+"追踪编号为空,请核实后再操作!");
                    }
                    fbaLogsticsOrderBoxNewList.get(i).setTrackingCode(stateTrackCodeList.get(i));

                    //修改头程箱子数据
                    FbaLogsticsOrderBox fbaLogsticsOrderBox = fbaLogsticsOrderBoxNewList.get(i);
                    findFbaBoxDetailNewList = findFbaBoxDetailBatchList.stream().filter(fbaBoxDetail -> fbaBoxDetail.getCodeBox().equals(fbaLogsticsOrderBox.getCodeBox()
                    )).collect(Collectors.toList());
                    findFbaBoxDetailNewList.get(i).setTrackingCode(stateTrackCodeList.get(i));
                }
            }
        }
        //修改物流箱子信息
        if (fbaLogsticsOrderBoxBatch.size() != 0){
            int updateLength = fbaLogsticsOrderBoxBatch.size();
            int i = 0;
            while (updateLength > 50) {
                fbaLogsticsOrderBoxMapper.batchUpdate(fbaLogsticsOrderBoxBatch.subList(i, i + 50));
                i = i + 50;
                updateLength = updateLength - 50;
            }
            if (updateLength > 0) {
                fbaLogsticsOrderBoxMapper.batchUpdate(fbaLogsticsOrderBoxBatch.subList(i, i + updateLength));
            }
        }

        //修改物流详情信息
        if (fbaLogsticsOrderDetailBatch.size() != 0){
            int updateLength = fbaLogsticsOrderDetailBatch.size();
            int i = 0;
            while (updateLength > 90) {
                fbaLogsticsOrderDetailMapper.batchUpdateNew(fbaLogsticsOrderDetailBatch.subList(i, i + 90));
                i = i + 90;
                updateLength = updateLength - 90;
            }
            if (updateLength > 0) {
                fbaLogsticsOrderDetailMapper.batchUpdateNew(fbaLogsticsOrderDetailBatch.subList(i, i + updateLength));
            }
        }
        //修改物流信息
        fbaLogsticsOrderMapper.batchUpdate(fbaLogsticsOrderBatch);
        //修改头程完成单数据
        fbaBoxMapper.updateBatchCode(findFbaBoxBatchList);

        //修改头程箱子数据
        if (findFbaBoxDetailBatchList.size() != 0){
            int updateLength = findFbaBoxDetailBatchList.size();
            int i = 0;
            while (updateLength > 50) {
                fbaBoxDetailMapper.batchUpdate(findFbaBoxDetailBatchList.subList(i, i + 50));
                i = i + 50;
                updateLength = updateLength - 50;
            }
            if (updateLength > 0) {
                fbaBoxDetailMapper.batchUpdate(findFbaBoxDetailBatchList.subList(i, i + updateLength));
            }
        }
        return AjaxResult.success("导入成功");
    }

    /**
     * ex表格导入多个FBA设置状态为标记发货
     * @return
     * @author fxz
     */
    @Override
    @Transactional(timeout=120,propagation= Propagation.REQUIRES_NEW)
    public AjaxResult batchDelivery(Map<String, Object> params) {
        JSONArray jsonArray = JSON.parseArray(params.get("infoItems").toString());
        List<FbaLogsticsOrder> fbaLogsticsOrderList = new ArrayList<>();
        FbaLogsticsOrder fbaLogsticsOrder1;
        for (int i = 0; i < jsonArray.size(); i++) {
            JSONObject jsonObject = (JSONObject) jsonArray.get(i);
            if (jsonObject.get("code") != null){
                fbaLogsticsOrder1 = new FbaLogsticsOrder();
                fbaLogsticsOrder1.setCode(jsonObject.get("code").toString());
                fbaLogsticsOrderList.add(fbaLogsticsOrder1);
            }
        }
        if (fbaLogsticsOrderList.size() == 0){
            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
            return AjaxResult.error("数据不存在,请核实后再操作!");
        }
        //物流订单信息
        List<FbaLogsticsOrder> fbaLogsticsOrderBatch = fbaLogsticsOrderMapper.findFbaLogsticsOrderBatch(fbaLogsticsOrderList);
        if (fbaLogsticsOrderBatch.size() == 0){
            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
            return AjaxResult.error("订单不存在,请核实后再操作!");
        }

        //筛选出不符合修改的fba数据
        List<FbaLogsticsOrder> collect = new ArrayList<>();
        for (FbaLogsticsOrder fbaLogsticsOrder : fbaLogsticsOrderList){
            List<FbaLogsticsOrder> existList = fbaLogsticsOrderBatch.stream().filter(fbaLogsticsOrder3 -> fbaLogsticsOrder3.getCode().equals(fbaLogsticsOrder.getCode()))
                    .collect(Collectors.toList());
            if (existList.size() == 0){
                collect.add(fbaLogsticsOrder);
            }else {
                if (!existList.get(0).getStateTrack().equals("2")){
                    collect.add(fbaLogsticsOrder);
                }
            }
        }

        //筛选满足物流状态为发票清单已打印
        List<FbaLogsticsOrder> fbaLogsticsOrderBatchNew = fbaLogsticsOrderBatch.stream().filter(fbaLogsticsOrder -> "2".equals(fbaLogsticsOrder.getStateTrack()))
                .collect(Collectors.toList());
        for (FbaLogsticsOrder fbaLogsticsOrder : fbaLogsticsOrderBatchNew){
            //修改发票清单状态
            if (fbaLogsticsOrder.getStateTrack().equals("2")){
                fbaLogsticsOrder.setStateTrack("3");
                fbaLogsticsOrder.setStateTrackName("物流已发货");
            }
        }


        List<FbaLogsticsOrderDetail> fbaLogsticsOrderDetailList = fbaLogsticsOrderDetailMapper.findFbaLogsticsOrderDetailBatch(fbaLogsticsOrderList);
        FbaBox fbaBox1;
        List<FbaBox> fbaBoxList = new ArrayList<>();
        for (FbaLogsticsOrderDetail fbaLogsticsOrderDetail : fbaLogsticsOrderDetailList){
            if (fbaLogsticsOrderDetail.getStateTrack().equals("2")){
                fbaLogsticsOrderDetail.setStateTrack("3");
                fbaLogsticsOrderDetail.setStateTrackName("物流已发货");
                fbaLogsticsOrderDetail.setDeliveryTime(new Date());
                fbaLogsticsOrderDetail.setDeliveryUser(ShiroUtils.getUserName());
            }
            fbaBox1 = new FbaBox();
            fbaBox1.setCode(fbaLogsticsOrderDetail.getCode());
            fbaBoxList.add(fbaBox1);
        }
        List<FbaBox> fbaBox1List = fbaBoxMapper.findFbaBoxBatch(fbaBoxList);
        List<FbaBoxDetail> fbaBoxDetailList = fbaBoxDetailMapper.fbaBoxDetailBatchList(fbaBoxList);
        List<FbaBox> fbaBox1ListNew = new ArrayList<>();
        List<FbaBoxDetail> existList;
        for (FbaBox fbaBox : fbaBox1List) {
            if (fbaBox.getStateTrack().equals("2")) {
                fbaBox.setStateTrack("3");
                fbaBox.setStateTrackName("物流已发货");
                fbaBox.setDeliveryTime(new Date());
                fbaBox.setDeliveryUser(ShiroUtils.getUserName());
                fbaBox1ListNew.add(fbaBox);

                existList = fbaBoxDetailList.stream().filter(fbaBoxDetail -> fbaBoxDetail.getCodeBox().equals(fbaBox.getCode()))
                        .collect(Collectors.toList());
                //新增fba入仓信息
                addFbaWarehousing(fbaBox,existList);
            }
        }

        //修改物流状态为发票清单已导入
        if (fbaBox1ListNew.size() != 0){
            fbaBoxMapper.updateBatchGuid(fbaBox1ListNew);
        }

        if (fbaLogsticsOrderBatch.size() != 0){
            fbaLogsticsOrderMapper.batchUpdate(fbaLogsticsOrderBatch);
        }
        if (fbaLogsticsOrderDetailList.size() != 0){
            int updateLength = fbaLogsticsOrderDetailList.size();
            int i = 0;
            while (updateLength > 90) {
                fbaLogsticsOrderDetailMapper.batchUpdateNew(fbaLogsticsOrderDetailList.subList(i, i + 90));
                i = i + 90;
                updateLength = updateLength - 90;
            }
            if (updateLength > 0) {
                fbaLogsticsOrderDetailMapper.batchUpdateNew(fbaLogsticsOrderDetailList.subList(i, i + updateLength));
            }
        }

        //将修改的提示出来
        if (collect.size() > 0){
            params.put("getFieldAttributesType", "export");
            List<SysDictionaryForExport> fieldList = new ArrayList<>();
            SysDictionaryForExport sysDictionaryForExport = new SysDictionaryForExport();
            sysDictionaryForExport.setGuid(RandomValueUtils.getGUID());
            sysDictionaryForExport.setModuleTable("TP_FBALOGSTICSORDER");
            sysDictionaryForExport.setFieldName("物流订单号");
            sysDictionaryForExport.setFields("code");
            sysDictionaryForExport.setFieldFrontType("t_test");
            sysDictionaryForExport.setFieldFrontTemplet("t_test");
            sysDictionaryForExport.setFieldFrontOrder(1);
            fieldList.add(sysDictionaryForExport);
            ExportExcelUtils<FbaLogsticsOrder> util = new ExportExcelUtils<FbaLogsticsOrder>("TP_FBALOGSTICSORDER", new FbaLogsticsOrder());
            AjaxResult ajaxResult = util.exportExcel(collect, fieldList, "标记发货错误数据", "export");
            return new AjaxResult(400,"批量标记发货部分失败,具体请看标记发货错误数据表格!",ajaxResult.get("msg"));
        }
        return AjaxResult.success("批量标记发货成功");
    }

    /**
     * 标记发货
     * @param params
     * @return
     */
    @Override
    @Transactional(timeout=120,propagation= Propagation.REQUIRES_NEW)
    public AjaxResult isShipments(Map<String, Object> params) {
        List<FbaLogsticsOrder> fbaLogsticsOrderList =  JSON.parseArray(params.get("infoItems").toString(), FbaLogsticsOrder.class);
        if (fbaLogsticsOrderList.size() == 0){
            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
            return AjaxResult.error("物流订单数据不存在!");
        }
        List<FbaLogsticsOrder> fbaLogsticsOrderListNew = fbaLogsticsOrderMapper.findFbaLogsticsOrderBatch(fbaLogsticsOrderList);
        for (FbaLogsticsOrder fbaLogsticsOrder : fbaLogsticsOrderListNew){
            //修改发票清单状态
            if (fbaLogsticsOrder.getStateTrack().equals("2")){
                fbaLogsticsOrder.setStateTrack("3");
                fbaLogsticsOrder.setStateTrackName("物流已发货");
            }
        }
        List<FbaLogsticsOrderDetail> fbaLogsticsOrderDetailList = fbaLogsticsOrderDetailMapper.findFbaLogsticsOrderDetailBatch(fbaLogsticsOrderList);
        FbaBox fbaBox1;
        List<FbaBox> fbaBoxList = new ArrayList<>();
        for (FbaLogsticsOrderDetail fbaLogsticsOrderDetail : fbaLogsticsOrderDetailList){
            if (fbaLogsticsOrderDetail.getStateTrack().equals("2")){
                fbaLogsticsOrderDetail.setStateTrack("3");
                fbaLogsticsOrderDetail.setStateTrackName("物流已发货");
                fbaLogsticsOrderDetail.setDeliveryTime(new Date());
                fbaLogsticsOrderDetail.setDeliveryUser(ShiroUtils.getUserName());
            }
            fbaBox1 = new FbaBox();
            fbaBox1.setCode(fbaLogsticsOrderDetail.getCode());
            fbaBoxList.add(fbaBox1);
        }
        List<FbaBox> fbaBox1List = fbaBoxMapper.findFbaBoxBatch(fbaBoxList);
        if (fbaBoxList.size() != fbaBox1List.size()){
            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
            return AjaxResult.error("选择的头程装箱单不存在,请核实后在操作!");
        }
        List<FbaBox> fbaBox1ListNew = new ArrayList<>();
        List<FbaBoxDetail> fbaBoxDetailList = fbaBoxDetailMapper.fbaBoxDetailBatchList(fbaBoxList);
        List<FbaBoxDetail> existList;
        for (FbaBox fbaBox : fbaBox1List) {
            if (fbaBox == null){
                TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
                return AjaxResult.error("头程装箱单不存在,请核实后在操作!");
            }
            if (!fbaBox.getStateTrack().equals("2")){
                TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
                return AjaxResult.error("选择单据中存在不是发票清单已打出状态,请核实后在操作!");
            }
            if (fbaBox.getStateTrack().equals("2")) {
                fbaBox.setStateTrack("3");
                fbaBox.setStateTrackName("物流已发货");
                fbaBox.setDeliveryTime(new Date());
                fbaBox.setDeliveryUser(ShiroUtils.getUserName());
                fbaBox1ListNew.add(fbaBox);


                existList = fbaBoxDetailList.stream().filter(fbaBoxDetail -> fbaBoxDetail.getCodeBox().equals(fbaBox.getCode()))
                        .collect(Collectors.toList());
                //新增fba入仓信息
                addFbaWarehousing(fbaBox,existList);
            }
        }

        //修改物流状态为发票清单已导入
        if (fbaBox1ListNew.size() != 0){
            fbaBoxMapper.updateBatchGuid(fbaBox1ListNew);
        }

        if (fbaLogsticsOrderListNew.size() != 0){
            fbaLogsticsOrderMapper.batchUpdate(fbaLogsticsOrderListNew);
        }
        if (fbaLogsticsOrderDetailList.size() != 0){
            fbaLogsticsOrderDetailMapper.batchUpdate(fbaLogsticsOrderDetailList);
        }

        return AjaxResult.success(MessageUtils.message("module.success.issuccess"));
    }

    /**
     * 回退
     * @param params
     * @return
     */
    @Override
    @Transactional(timeout=120,propagation= Propagation.REQUIRES_NEW)
    public AjaxResult htbtn(Map<String, Object> params) {
        List<FbaLogsticsOrder> fbaLogsticsOrderList =  JSON.parseArray(params.get("infoItems").toString(), FbaLogsticsOrder.class);
        List<FbaLogsticsOrder> fbaLogsticsOrderListNew = fbaLogsticsOrderMapper.findFbaLogsticsOrderBatch(fbaLogsticsOrderList);
        if (params.get("type").equals("0")) {
            for (FbaLogsticsOrder fbaLogsticsOrder : fbaLogsticsOrderListNew) {
                //判断是否允许回退
                if (fbaLogsticsOrder.getStateTrack().equals("4") || fbaLogsticsOrder.getStateTrack().equals("5")) {
                    TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
                    return AjaxResult.error("选择单据中存在追踪编号已导入或者C88文件已导入,不允许回退!");
                }
            }
        }
        //物流详情信息
        List<FbaLogsticsOrderDetail> fbaLogsticsOrderDetailBatch = fbaLogsticsOrderDetailMapper.findFbaLogsticsOrderDetailBatch(fbaLogsticsOrderList);
        List<String> codes = new ArrayList<>();
        List<String> fbaSpellTicketGuids = new ArrayList<>();
        List<FbaBox> fbaBoxList = new ArrayList<>();
        List<String> ids = new ArrayList<>();
        String [] arr1;
        FbaBox fbaBox;
        SysAccountBandDept sysAccountBandDept;
        AccountCountForCompany logisticsAccount;
        AccountCountForCompany operationAccount;
        AccountDetailedForCompany account;
        AccountDetailedForCompany account1;
        List<AccountDetailedForCompany> accountList =new ArrayList<>();
        for (FbaLogsticsOrder fbaLogsticsOrder : fbaLogsticsOrderList){
            codes.add(fbaLogsticsOrder.getCode());
            fbaSpellTicketGuids.add(fbaLogsticsOrder.getFbaSpellTicketGuid());
            arr1=fbaLogsticsOrder.getFbaSpellTicketGuid().split(",");
            for (String id : arr1){
                ids.add( id);
            }
        }

        List<FbaLogsticsOrder> fbaLogsticsOrderListNew2 = new ArrayList<>();
        for (FbaLogsticsOrderDetail fbaLogsticsOrderDetail : fbaLogsticsOrderDetailBatch){
            fbaBox = new FbaBox();
            fbaBox.setCode(fbaLogsticsOrderDetail.getCode());
            fbaBox.setStateTrack("1");
            fbaBox.setStateTrackName("已交付物流部");
            fbaBox.setDeliveryTime(null);
            fbaBox.setDeliveryUser(null);
            fbaBoxList.add(fbaBox);

            //删除入仓单和详情
            FbaWarehousing fbaWarehousing = fbaWarehousingMapper.queryByCodeBoxFrost(fbaBox.getCode());
            if(fbaWarehousing != null){
                fbaWarehousingMapper.deleteCodeBox(fbaWarehousing.getCodeBox());
                fbaWarehousingDetailMapper.deleteCode(fbaWarehousing.getBid());
            }

            if (fbaLogsticsOrderDetail.getLoadDelivery() != null) {
                if (fbaLogsticsOrderDetail.getLoadDelivery().equals("是")) {
                    // 查询物流部账号
                    sysAccountBandDept = sysAccountBandDeptMapper.findByAccountId("WLB");
                    if (sysAccountBandDept == null) {
                        TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
                        return AjaxResult.error("物流部门绑定账户未启用或不存在,请核实后再操作!");
                    }
                    // 物流公司
                    logisticsAccount = accountCountForCompanyMapper.queryFirstByVGsBm(sysAccountBandDept.getvGsBm());
                    if (logisticsAccount == null) {
                        TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
                        return AjaxResult.error("物流公司实时资金不存在,请核实后再操作!");
                    }
//                     运营公司
                    operationAccount = accountCountForCompanyMapper.queryFirstByVGsBm(fbaLogsticsOrderDetail.getCodeCompany());
                    if (operationAccount == null) {
                        TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
                        return AjaxResult.error("运营公司实时资金不存在,请核实后再操作!");
                    }
                    fbaLogsticsOrderListNew2 = fbaLogsticsOrderListNew.stream().
                            filter(fbaLogsticsOrder -> fbaLogsticsOrder.getCode().equals(fbaLogsticsOrderDetail.getCodeFbaLogsticsOrder())
                            ).collect(Collectors.toList());
                    //资金流水(物流部 -运营公司头程物流费)
                    account = new AccountDetailedForCompany();
                    account.setGuid(RandomValueUtils.getGUID());
                    account.setvGsBm(operationAccount.getvGsBm());
                    account.setvGsMc(operationAccount.getvGsMc());
                    account.setvGsJc(operationAccount.getvGsJc());
                    account.setvGsBmP(logisticsAccount.getvGsBm());
                    account.setvGsMcP(logisticsAccount.getvGsMc());
                    account.setvGsJcP(logisticsAccount.getvGsJc());
                    account.setvJyLx("物流服务→头程运费");
                    account.setfJe(Double.valueOf((fbaLogsticsOrderDetail.getActualLogisticsFreightPf().subtract(fbaLogsticsOrderDetail.getActualLogisticsFreight()).toString())));
                    account.setdJySj(fbaLogsticsOrderDetail.getCreateTime());
                    account.setdShSj(new Date());
                    account.setvBz("（退回）头程物流抛货补收费用" + ",头程计划单:" + fbaLogsticsOrderDetail.getCodePlan() + "," + fbaLogsticsOrderDetail.getShipmentId() + "," + fbaLogsticsOrderDetail.getNameLogsticsWay());
                    account.setCreateTime(new Date());
                    accountList.add(account);

                    //资金流水(物流部 -运营公司头程物流费)
                    account1 = new AccountDetailedForCompany();
                    account1.setGuid(RandomValueUtils.getGUID());
                    account1.setvGsBm(logisticsAccount.getvGsBm());
                    account1.setvGsMc(logisticsAccount.getvGsMc());
                    account1.setvGsJc(logisticsAccount.getvGsJc());
                    account1.setvGsBmP(operationAccount.getvGsBm());
                    account1.setvGsMcP(operationAccount.getvGsMc());
                    account1.setvGsJcP(operationAccount.getvGsJc());
                    account1.setvJyLx("物流服务→头程运费");
                    account1.setfJe(-Double.valueOf((fbaLogsticsOrderDetail.getActualLogisticsFreightPf().subtract(fbaLogsticsOrderDetail.getActualLogisticsFreight()).toString())));
                    account1.setdJySj(fbaLogsticsOrderDetail.getCreateTime());
                    account1.setdShSj(new Date());
                    account1.setvBz("（退回）头程物流抛货补收费用" + ",头程计划单:" + fbaLogsticsOrderDetail.getCodePlan() + "," + fbaLogsticsOrderDetail.getShipmentId() + "," + fbaLogsticsOrderDetail.getNameLogsticsWay());
                    account1.setCreateTime(new Date());
                    accountList.add(account1);
                }
            }
        }
        if (accountList.size() != 0){
            businessMapper.insertBatchNew(accountList);
        }

        //删除物流信息
        fbaLogsticsOrderMapper.deleteByCodes(codes);
        //删除物流详情
        fbaLogsticsOrderDetailMapper.deleteByCodes(codes);
        //删除物流箱子信息
        fbaLogsticsOrderBoxMapper.deleteByCodes(codes);
        // 批量修改头物流拼票数据的未拼票状态
        fbaSpellTickMapper.updateBatchGuid(ids);
        // 批量修改头程装箱数据的发货状态
        fbaBoxMapper.updateBatchCode(fbaBoxList);
        return AjaxResult.success("回退成功");
    }

    /**
     * 导入运单号
     * @param params
     * @return
     */
    @Override
    @Transactional(timeout=120,propagation= Propagation.REQUIRES_NEW)
    public AjaxResult waybillNumber(Map<String, Object> params) {
        List<FbaLogsticsOrder> fbaLogsticsOrderList =  JSON.parseArray(params.get("infoItems").toString(), FbaLogsticsOrder.class);
        FbaLogsticsOrder fbaLogsticsOrder = fbaLogsticsOrderList.get(0);
        fbaLogsticsOrder.setWaybillNumber(params.get("waybillNumber").toString());
        // 查询是否重复
        int count = fbaLogsticsOrderMapper.countFbaLogsticsOrder(fbaLogsticsOrder);
        if (count != 0){
            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
            return AjaxResult.error("选择单据中运单号已重复导入,请核实后再操作!");
        }
        FbaLogsticsOrder fbaLogsticsOrderNew  = fbaLogsticsOrderMapper.queryFirstByGuid(fbaLogsticsOrder.getGuid());
        if (!fbaLogsticsOrderNew.getState().equals("0")){
            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
            return AjaxResult.error("选择单据订单状态不允许导入运单号,请核实后在操作!");
        }
        fbaLogsticsOrderNew.setWaybillNumber(params.get("waybillNumber").toString());
        fbaLogsticsOrderMapper.updateByGuid(fbaLogsticsOrderNew);
        return AjaxResult.success("导入运单号成功!");
    }


    /**
     * 物流订单上传C88数据
     * @param params
     * @return
     */
    @Override
    @Transactional(timeout=120,propagation= Propagation.REQUIRES_NEW)
    public AjaxResult entryNumber(Map<String, Object> params) {
        Date date = new Date();
        FbaLogsticsOrder fbaLogsticsOrder = fbaLogsticsOrderMapper.queryFirstByGuid(String.valueOf(params.get("guid")));
        if (fbaLogsticsOrder == null){
            return AjaxResult.error("数据不存在,请核实后再操作!");
        }
        //根据明细将头程的c88数据回填
        List<FbaLogsticsOrderDetail> fbaLogsticsOrderDetailList = fbaLogsticsOrderDetailMapper.findCode(fbaLogsticsOrder.getCode());
        if (fbaLogsticsOrderDetailList.size() > 1){
            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
            return AjaxResult.error("订单明细多条不允许上传C88,请核实后再操作!");
        }
        FbaBox fbaBox = fbaBoxMapper.queryFirstByShipmentId(fbaLogsticsOrderDetailList.get(0).getShipmentId());
        if (fbaBox == null){
            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
            return AjaxResult.error("头程数据不存在,请核实后再操作!");
        }
        fbaBox.setEntryNumber(String.valueOf(params.get("entryNumber")));
        fbaBox.setCdrTime(date);
        fbaBox.setStateTrackName("C88文件已导入");
        fbaBox.setStateTrack("5");
        fbaBoxMapper.updateByGuid(fbaBox);

        fbaLogsticsOrder.setEntryNumber(String.valueOf(params.get("entryNumber")));
        fbaLogsticsOrder.setCdrTime(date);
        fbaLogsticsOrder.setStateTrackName("C88文件已导入");
        fbaLogsticsOrder.setStateTrack("5");
        fbaLogsticsOrder.setFbaBoxCode(fbaBox.getBid());
        fbaLogsticsOrderMapper.updateByGuid(fbaLogsticsOrder);
        return AjaxResult.success("操作成功!");
    }


    /**
     * 批量导入运单号状态
     * @return
     * @author fxz
     */
    @Override
    @Transactional(timeout=120,propagation= Propagation.REQUIRES_NEW)
    public AjaxResult importWaybillNumber(Map<String, Object> params) {
        JSONArray jsonArray = JSON.parseArray(params.get("infoItems").toString());
        List<FbaLogsticsOrder> fbaLogsticsOrderList = new ArrayList<>();
        FbaLogsticsOrder fbaLogsticsOrder1;
        for (int i = 0; i < jsonArray.size(); i++) {
            JSONObject jsonObject = (JSONObject) jsonArray.get(i);
            if (jsonObject.get("code") != null){
                fbaLogsticsOrder1 = new FbaLogsticsOrder();
                fbaLogsticsOrder1.setCode(jsonObject.get("code").toString());
                fbaLogsticsOrder1.setWaybillNumber(jsonObject.get("waybillNumber").toString());
                fbaLogsticsOrderList.add(fbaLogsticsOrder1);
            }
        }
        if (fbaLogsticsOrderList.size() == 0){
            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
            return AjaxResult.error("数据不存在,请核实后再操作!");
        }
        //排除运单号重复且状态不为无的时候
        List<FbaLogsticsOrder> repetitionList = fbaLogsticsOrderMapper.repetitionList(fbaLogsticsOrderList);
        //物流订单信息
        List<FbaLogsticsOrder> fbaLogsticsOrderBatch = fbaLogsticsOrderMapper.findFbaLogsticsOrderBatch(fbaLogsticsOrderList);
        if (fbaLogsticsOrderBatch.size() == 0){
            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
            return AjaxResult.error("订单不存在,请核实后再操作!");
        }

        //错误的数据
        List<FbaLogsticsOrder> collect = new ArrayList<>();
        //修改的数据
        List<FbaLogsticsOrder> fbaLogsticsOrderList1 = new ArrayList<>();
        List<FbaLogsticsOrder> collectNew;
        List<FbaLogsticsOrder> fbaLogsticsOrderBatchNew;
        //匹配出符合修改的物流运单号
        for (FbaLogsticsOrder fbaLogsticsOrder : fbaLogsticsOrderList){
            //判断是否有重复
            collectNew = repetitionList.stream().filter(
                    fbaLogsticsOrderNew -> fbaLogsticsOrder.getWaybillNumber().equals(fbaLogsticsOrderNew.getWaybillNumber())
                    && fbaLogsticsOrder.getCode().equals(fbaLogsticsOrderNew.getCode())).collect(Collectors.toList());

            //添加错误的数据
            if (collectNew.size() == 0 && repetitionList.size() != 0){
                collect.add(fbaLogsticsOrder);
            } else {
                fbaLogsticsOrderBatchNew = fbaLogsticsOrderBatch.stream().filter(
                        fbaLogsticsOrderNew -> fbaLogsticsOrder.getCode().equals(fbaLogsticsOrderNew.getCode())
                        )
                        .collect(Collectors.toList());
                if (fbaLogsticsOrderBatchNew.size() != 0){
                    //判断是否订单状态！=无
                    if (!fbaLogsticsOrderBatchNew.get(0).getState().equals("0")){
                        collect.add(fbaLogsticsOrderBatchNew.get(0));
                    }else {
                        fbaLogsticsOrderBatchNew.get(0).setWaybillNumber(fbaLogsticsOrder.getWaybillNumber());
                        fbaLogsticsOrderList1.add(fbaLogsticsOrderBatchNew.get(0));
                    }
                }
            }
        }


        if (fbaLogsticsOrderBatch.size() != 0){
            fbaLogsticsOrderMapper.batchUpdate(fbaLogsticsOrderList1);
        }

        //将修改的提示出来
        if (collect.size() > 0){
            params.put("getFieldAttributesType", "export");
            List<SysDictionaryForExport> fieldList = new ArrayList<>();
            SysDictionaryForExport sysDictionaryForExport = new SysDictionaryForExport();
            sysDictionaryForExport.setGuid(RandomValueUtils.getGUID());
            sysDictionaryForExport.setModuleTable("TP_FBALOGSTICSORDER");
            sysDictionaryForExport.setFieldName("物流订单号");
            sysDictionaryForExport.setFields("code");
            sysDictionaryForExport.setFieldFrontType("t_test");
            sysDictionaryForExport.setFieldFrontTemplet("t_test");
            sysDictionaryForExport.setFieldFrontOrder(1);
            fieldList.add(sysDictionaryForExport);
            ExportExcelUtils<FbaLogsticsOrder> util = new ExportExcelUtils<FbaLogsticsOrder>("TP_FBALOGSTICSORDER", new FbaLogsticsOrder());
            AjaxResult ajaxResult = util.exportExcel(collect, fieldList, "导入运单号错误数据", "export");
            return new AjaxResult(400,"批量导入运单号部分失败,具体请看导入运单号错误数据表格!",ajaxResult.get("msg"));
        }
        return AjaxResult.success("批量导入运单号成功");
    }

    /**
     * 物流对账调用数据
     * @param params
     * @return
     * @throws Exception
     */
    @Override
    public List<FbaLogsticsOrderSelect> queryListSelect(Map<String, Object> params) throws Exception {
        params.put(DataFiltering.TABLE_ALIAS, "t1");	//查询表别名
        params.put(DataFiltering.DATA_WHERE, dictionaryForFieldService.getWhereStr(params));	//常规查询条件SQL语句
        params.put(DataFiltering.DATA_SCODE, dictionaryForFieldService.getDataScode(params));	//数据权限查询条件SQL语句

        PageUtils.startPage();

        return fbaLogsticsOrderMapper.findFbaLogsticsOrderSelect(params);
    }

    /**
     * 查询物流订单详情
     * @param params
     * @return
     * @throws Exception
     */
    @Override
    public List<FbaSpellTicket> details(Map<String, Object> params) throws Exception {
        FbaLogsticsOrder fbaLogsticsOrder = fbaLogsticsOrderMapper.queryFirstByLogisticsOrderCode(params.get("logisticsOrderCode").toString());
        String[] guidListSplit = fbaLogsticsOrder.getFbaSpellTicketGuid().split(",");
        params.put("guidListSplit",guidListSplit);
        //查询拼票数据
        List<FbaSpellTicket> fbaSpellTicketList = fbaSpellTickMapper.select2(params);

        //根据主表的guid查询对应箱子所以信息
        List<FbaSpellTicketDetail> fbaSpellTicketDetail = fbaSpellTicketDetailMapper.findFbaSpellTicketDetail(params);

        //物流总费用
        BigDecimal actualLogisticsFreightTolOld = BigDecimal.ZERO;
        //计重费
        BigDecimal priceTranCalWeightPredictTolOld = BigDecimal.ZERO;
        BigDecimal priceQualityExtraPredictTolOld = BigDecimal.ZERO;
        BigDecimal priceProNameExtraPredictTolOld = BigDecimal.ZERO;
        BigDecimal priceClearanceOpeTolOld = BigDecimal.ZERO;
        //箱数
        Integer numBox = 0;
        //计重总计
        BigDecimal weightCalTol = BigDecimal.ZERO;
        //抛重总计
        BigDecimal weightVolTol = BigDecimal.ZERO;
        //实重总计
        BigDecimal weightRealTol = BigDecimal.ZERO;
        //品名总计相加
        Integer pMNum = 0;
        //品名材质
        Set<String> gradeNumNew = new HashSet<>();
        List<FbaSpellTicketDetail> fbaSpellTicketDetailNew;
        List<FbaSpellTicketDetail> fbaSpellTicketDetailOld;
        for (FbaSpellTicket fbaSpellTicket : fbaSpellTicketList){
            //将旧的金额计算
            actualLogisticsFreightTolOld = actualLogisticsFreightTolOld.add(fbaSpellTicket.getActualLogisticsFreight());
            priceTranCalWeightPredictTolOld = priceTranCalWeightPredictTolOld.add(fbaSpellTicket.getPriceTranCalWeightPredict());
            priceQualityExtraPredictTolOld = priceQualityExtraPredictTolOld.add(fbaSpellTicket.getPriceQualityExtraPredict());
            priceProNameExtraPredictTolOld = priceProNameExtraPredictTolOld.add(fbaSpellTicket.getPriceProNameExtraPredict());
            priceClearanceOpeTolOld = priceClearanceOpeTolOld.add(fbaSpellTicket.getPriceClearanceOpe());
            numBox = numBox + fbaSpellTicket.getNumBox();
            weightRealTol = weightRealTol.add(fbaSpellTicket.getWeightReal());
            weightCalTol = weightCalTol.add(fbaSpellTicket.getWeightCal());
            weightVolTol = weightVolTol.add(fbaSpellTicket.getWeightVol());
            pMNum = pMNum + fbaSpellTicket.getNumProName();

            fbaSpellTicketDetailNew = fbaSpellTicketDetail.stream().filter(fbaSpellTicketDeta -> fbaSpellTicketDeta.getFbaSpellTicketGuid().equals(fbaSpellTicket.getGuid())).collect(Collectors.toList());
            //实际的商品信息
            fbaSpellTicketDetailOld = JSON.parseArray(fbaSpellTicketDetailNew.get(0).getProductInfo(), FbaSpellTicketDetail.class);
            //计算材质最大值
            for (FbaSpellTicketDetail fbaSpellTicketDetail1 : fbaSpellTicketDetailOld){
                gradeNumNew.add(fbaSpellTicketDetail1.getNameMaterialCn());
            }
            //清除抛货信息
            fbaSpellTicket.setJettisonCoe(null);
        }
        FbaSpellTicket fbaSpellTicket;
        for (int i = 0; i < 3; i++) {
            fbaSpellTicket = new FbaSpellTicket();
            switch (i) {
                case 0:
                    fbaSpellTicket.setShipmentId("总计金额");
                    fbaSpellTicket.setActualLogisticsFreight(actualLogisticsFreightTolOld);
                    fbaSpellTicket.setPriceTranCalWeightPredict(priceTranCalWeightPredictTolOld);
                    fbaSpellTicket.setPriceQualityExtraPredict(priceQualityExtraPredictTolOld);
                    fbaSpellTicket.setPriceProNameExtraPredict(priceProNameExtraPredictTolOld);
                    fbaSpellTicket.setPriceClearanceOpe(priceClearanceOpeTolOld);
                    fbaSpellTicket.setWeightCal(weightRealTol);
                    fbaSpellTicket.setWeightReal(weightRealTol);
                    fbaSpellTicket.setWeightVol(weightVolTol);
                    fbaSpellTicket.setSurchargeMaterialMax(String.join(",", gradeNumNew));
                    fbaSpellTicket.setNumProName(pMNum);
                    fbaSpellTicket.setNumBox(numBox);
                    break;
                case 1:
                    fbaSpellTicket.setShipmentId("拼票金额");
                    fbaSpellTicket.setActualLogisticsFreight(fbaLogsticsOrder.getActualLogisticsFreight());
                    fbaSpellTicket.setPriceTranCalWeightPredict(fbaLogsticsOrder.getPriceTranCalWeightPredict());
                    fbaSpellTicket.setPriceQualityExtraPredict(fbaLogsticsOrder.getPriceQualityExtraPredict());
                    fbaSpellTicket.setPriceProNameExtraPredict(fbaLogsticsOrder.getPriceProNameExtraPredict());
                    fbaSpellTicket.setPriceClearanceOpe(fbaLogsticsOrder.getPriceClearanceOpe());
                    fbaSpellTicket.setWeightCal(fbaLogsticsOrder.getWeightCal());
                    fbaSpellTicket.setWeightReal(fbaLogsticsOrder.getWeightReal());
                    fbaSpellTicket.setWeightVol(fbaLogsticsOrder.getWeightVol());
                    fbaSpellTicket.setSurchargeMaterialMax(fbaLogsticsOrder.getSurchargeMaterialMax());
                    fbaSpellTicket.setNumProName(fbaLogsticsOrder.getNumProName());
                    fbaSpellTicket.setNumBox(numBox);
                    fbaSpellTicket.setJettisonCoe(fbaLogsticsOrder.getWeightVol().subtract(fbaLogsticsOrder.getWeightReal()));
                    fbaSpellTicket.setAveBoxWeight(fbaLogsticsOrder.getWeightReal().divide(new BigDecimal(numBox), 2, RoundingMode.HALF_UP));
                    break;
                case 2:
                    fbaSpellTicket.setShipmentId("差额金额");
                    fbaSpellTicket.setActualLogisticsFreight(actualLogisticsFreightTolOld.subtract(fbaLogsticsOrder.getActualLogisticsFreight()));
                    fbaSpellTicket.setPriceTranCalWeightPredict(priceTranCalWeightPredictTolOld.subtract(fbaLogsticsOrder.getPriceTranCalWeightPredict()));
                    fbaSpellTicket.setPriceQualityExtraPredict(priceQualityExtraPredictTolOld.subtract(fbaLogsticsOrder.getPriceQualityExtraPredict()));
                    fbaSpellTicket.setPriceProNameExtraPredict(priceProNameExtraPredictTolOld.subtract(fbaLogsticsOrder.getPriceProNameExtraPredict()));
                    fbaSpellTicket.setPriceClearanceOpe(priceClearanceOpeTolOld.subtract(fbaLogsticsOrder.getPriceClearanceOpe()));
                    fbaSpellTicket.setWeightCal(weightCalTol.subtract(fbaLogsticsOrder.getWeightCal()));
                    fbaSpellTicket.setWeightReal(weightRealTol.subtract(fbaLogsticsOrder.getWeightReal()));
                    fbaSpellTicket.setWeightVol(weightVolTol.subtract(fbaLogsticsOrder.getWeightVol()));
                    fbaSpellTicket.setSurchargeMaterialMax("无");
                    fbaSpellTicket.setNumProName(pMNum - fbaLogsticsOrder.getNumProName());
                    fbaSpellTicket.setNumBox(0);
                    break;
            }
            fbaSpellTicketList.add(fbaSpellTicket);
        }
        return fbaSpellTicketList;
    }

    /**
     * 添加图片
     * @return
     */
    private void  addPhotograph(HSSFSheet sheet ,HSSFWorkbook wb,String urls,Integer i) throws Exception{
        //给标题插入图片
        ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
//        URL url = new URL("http://cdn.mini-ease.com/profile/images/20230110/fa57b92248dbf21b479e7efd2cd25298.jpg");
        URL url = new URL(urls);
        BufferedImage bi = ImageIO.read(url);
        if (bi != null) {

            //获取长宽
//        Integer width = bi.getWidth();
//        Integer height = bi.getHeight();
            // 将图片写入流中
            ImageIO.write(bi, "png", byteArrayOut);
            // 利用HSSFPatriarch将图片写入EXCEL
            HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
            /**
             * 该构造函数有8个参数
             * int dx1, int dy1, int dx2, int dy2,
             * short col1, int row1, short col2, int row2
             * excel中的cellNum和rowNum的index都是从0开始的
             */
            //图片一导出到单元格中
            HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0,
                    (short) 28, i, (short) 29, i + 1);
            // 插入图片
            patriarch.createPicture(anchor, wb.addPicture(byteArrayOut
                    .toByteArray(), HSSFWorkbook.PICTURE_TYPE_PNG));
        }
    }


    private void addFbaWarehousing(FbaBox fbaBox, List<FbaBoxDetail> fbaBoxDetailList){
        FbaWarehousing fbaWarehousing = new FbaWarehousing();
        //查询头程货件最新数据
        Map<String, Object> params2 = new HashMap<String, Object>();
        params2.put("moduleTable", "TP_FbaWarehousing");
        ModuleNidAndBid moduleNidAndBid = moduleNidAndBidMapper.findModuleNidAndBid(params2);
        //新增fba入仓单数据
        fbaWarehousing.setShipmentId(fbaBox.getShipmentId());
        fbaWarehousing.setTrackingCode(fbaBox.getTrackingCode());
        fbaWarehousing.setDestinationFulfillmentCenterId(fbaBox.getDestinationFulfillmentCenterId());
        fbaWarehousing.setCodePlan(fbaBox.getCodePlan());
        fbaWarehousing.setCodeBox(fbaBox.getCode());
        fbaWarehousing.setCodeCompany(fbaBox.getCodeCompany());
        fbaWarehousing.setvGsJc(fbaBox.getvGsJc());
        fbaWarehousing.setSellerId(fbaBox.getSellerId());
        fbaWarehousing.setNameLogsticsWay(fbaBox.getNameLogsticsWay());
        fbaWarehousing.setNumBox(fbaBox.getNumBox());
        fbaWarehousing.setPackingTime(fbaBox.getUpdateTime());
        fbaWarehousing.setAuditTime(fbaBox.getAuditTime());
        fbaWarehousing.setStateName("处理中");
        fbaWarehousing.setNid(moduleNidAndBid.getNid());
        fbaWarehousing.setBid(moduleNidAndBid.getBid());
        fbaWarehousing.setCid(ShiroUtils.getMandatorCid());
        fbaWarehousing.setbUser(ShiroUtils.getMandatorId());
        fbaWarehousing.setbDept(ShiroUtils.getMandatorDeptPath());
        fbaWarehousing.setbPosition(ShiroUtils.getMandatorPositionNameDa());
        fbaWarehousing.setCreateTime(new Date());
        fbaWarehousing.setDeleted(false);
        fbaWarehousing.setCodeShipment(fbaBox.getCodeShipment());
        fbaWarehousing.setShopName(fbaBox.getShopName());
        fbaWarehousing.setCreationMethod(fbaBox.getCreationMethod());
        fbaWarehousingMapper.insert(fbaWarehousing);

        FbaWarehousingDetail fbaWarehousingDetail;
        for (FbaBoxDetail fbaBoxDetail : fbaBoxDetailList){
            //新增入仓单明细
            fbaWarehousingDetail = new FbaWarehousingDetail();
            fbaWarehousingDetail.setGuid(RandomValueUtils.getGUID());
            fbaWarehousingDetail.setCode(fbaWarehousing.getBid());
            fbaWarehousingDetail.setBoxIndex(fbaBoxDetail.getBoxIndex());
            fbaWarehousingDetail.setProductInfo(fbaBoxDetail.getProductInfo());
            fbaWarehousingDetail.setTrackingCode(fbaBoxDetail.getTrackingCode());
            fbaWarehousingDetailMapper.insert(fbaWarehousingDetail);
        }
    }
}
